package excel.read;

import java.io.InputStream;
import java.lang.SuppressWarnings;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.UUID;
import java.util.concurrent.BlockingQueue;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.concurrent.LinkedBlockingQueue;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;




/**
 * excel 工具类库入口
 * @author littlelot
 * @version 1.0.0
 */
public class IExcel<T> {
    /**
     * excel 源文件流
     */
    private final InputStream sourceStream;


    /**
     * 数据处理校验对象
     */
    private final TaskHandler<T> handler;


    /**
     * 读取数据处理对象
     */
    private final ExcelReadHandler excelReadHandler = new ExcelReadHandler();


    /**
     * 写入数据处理对象
     */
    private final DataWriteHandler dataWriteHandler = new DataWriteHandler();


    /**
     * 数据队列
     */
    private final BlockingQueue<List<TaskResultData<T>>> dataQueue = new LinkedBlockingQueue<>();


    /**
     * 读取失败数组
     */
    private final List<TaskResult<T>> failList = new CopyOnWriteArrayList<>();


    /**
     * 需要读取 excel 的总列数,由用户自定义
     */
    private final int requiredCols;


    /**
     * 读取数据的起始行,默认为第三行
     */
    private final int startRow;


    /**
     * 消费数据的缓冲区大小,默认为5000
     */
    private final int bufferSize;


    /**
     * 读取数据线程是否完成
     */
    private volatile boolean isEnd = false;


    /**
     * 消费数据线程是否完成
     */
    private volatile boolean writeEnd = false;


    /**
     * 成功消费的数据条数
     */
    private volatile int totalSuccess = 0;


    /**
     * 日期格式化对象
     */
    private final SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");


    /**
     * slf4j for logger
     */
    private static final Logger logger = LoggerFactory.getLogger(IExcel.class);


    /**
     * @param sourceStream excel 源文件输入流
     * @param requiredCols 需处理数据的列数
     * @param startRow 数据起始行
     * @param bufferSize 批处理的数据大小
     * @param handler Subclass Of TaskHandler
     */
    public IExcel(InputStream sourceStream, int requiredCols, int startRow, int bufferSize, TaskHandler<T> handler) {
        this.sourceStream = sourceStream;
        this.requiredCols = requiredCols <= 0 ? 0 : requiredCols;
        this.startRow = startRow < 1 ? 1 : startRow;
        this.bufferSize = bufferSize < 1000 ? 1000 : bufferSize;
        this.handler = handler;
        logger.info("apply params for initial. requiredCols: {} startRow: {} bufferSize: {}", requiredCols, startRow, bufferSize);
    }


    public IExcel(InputStream sourceStream, TaskHandler<T> handler) {
        this(sourceStream, 0, 3, 5000, handler);
        logger.debug("apply default params for initial. requiredCols: {} startRow: {} bufferSize: {}", requiredCols, startRow, bufferSize);
    }


    /**
     * excel 读取类
     */
    private class ExcelReadHandler extends DefaultHandler {
        /**
         * a sharedStringsTable for current worksheet
         */
        private SharedStringsTable sst;

        private String lastContents;

        private CellDataType nextDataType = CellDataType.SSTINDEX;

        private String formatString;

        private short formatIndex;

        private StylesTable stylesTable;

        private final DataFormatter formatter = new DataFormatter();

        private int sheetIndex = -1;

        /**
         * 标题行
         */
        private List<String> titlelist = null;

        /**
         * 行数据
         */
        private List<String> rowlist = new ArrayList<>();

        private List<TaskResultData<T>> datalist = new ArrayList<>();

        private int total = 0;

        /**
         * 逻辑行的当前行,实际行数减一
         */
        private int curRow = 0;

        /**
         * 实际行的前一行id,用来判断空行
         */
        private String preRowNum = "";

        /**
         * 实际行的当前行id,用来判断空行
         */
        private String curRowNum = "";

        /**
         * 逻辑列的当前列,实际列数减一
         */
        private int curCol = 0;

        /**
         * 实际列的前一列id,用来判断空单元格
         */
        private String preXy = "";

        /**
         * 实际列的当前列id,用来判断空单元格
         */
        private String curXy = "";

        /**
         * excel 文件的边界值
         */
        private String dimension = "";

        /**
         * 单个sheet的总列数
         */
        private int maxCols = 0;

        /**
         * 单个sheet的总行数
         */
        private int maxRows = 0;

        /**
         * 实际处理的列数
         */
        private int hCols = 0;


        /**
         * 读取所有工作簿的入口方法
         * @throws Exception
         */
        public void process() throws Exception {
            OPCPackage pkg = OPCPackage.open(sourceStream);
            XSSFReader r = new XSSFReader(pkg);
            stylesTable = r.getStylesTable();
            SharedStringsTable sst = r.getSharedStringsTable();
            XMLReader parser = fetchSheetParser(sst);
            Iterator<InputStream> sheets = r.getSheetsData();

            while (sheets.hasNext()) {
                if (++sheetIndex == 1) break; // 只读取sheetIndex为0的sheet的数据
                logger.debug("read sheet{}", sheetIndex);
                InputStream sheet = sheets.next();
                InputSource sheetSource = new InputSource(sheet);
                parser.parse(sheetSource);
                sheet.close();
            }
        }


        /**
         * 该方法自动被调用，每读一行调用一次，在方法中写自己的业务逻辑即可
         * @param sheetIndex 工作簿序号
         * @param curRow 处理到第几行
         * @param rowlist 当前数据行的数据集合
         */
        private void optRow(int sheetIndex, int curRow, List<String> rowlist) {
            if (startRow == 1 && curRow == startRow - 1 || curRow == startRow - 2) {
                titlelist = new ArrayList<>(rowlist);
                logger.debug("titlelist: {}", titlelist);
            }

            if (curRow >= startRow - 1) {
                if (datalist.size() >= bufferSize || rowlist.size() == 0) {
                    logger.debug("datalist-size: {}", datalist.size());
                    try {
                        dataQueue.put(datalist);
                    } catch (InterruptedException e) {
                    }
                    datalist = new ArrayList<>();
                }
                logger.debug("check if parse correctly. rowlist-size: {} hCols: {}", rowlist.size(), hCols);
                logger.debug("rowlist: {}", rowlist);
                if (rowlist.size() >= hCols) {
                    String date = sdf.format(new Date());
                    TaskResultData<T> model = new TaskResultData<>();
                    TaskResult<T> failModel = new TaskResult<>();

                    try {
                        T data = handler.handleData(rowlist);

                        model.setSheetIndex(sheetIndex);
                        model.setCurRow(curRow);
                        model.setData(data);

                        datalist.add(model);
                    } catch (Exception e) {
                        logger.debug("catch customer error. exception: {}", e);
                        model.setFailObject(createFailObject(titlelist, rowlist));
                        model.setSheetIndex(sheetIndex);
                        model.setCurRow(curRow);

                        failModel.setOuterError(model);
                        failModel.setFailMsg(e.getMessage());
                        failModel.setLogTime(date);
                        failModel.setType(1);
                        failList.add(failModel);
                    }
                }
            }
        }

        private Map<String, String> createFailObject(List<String> titlelist, List<String> rowlist) {
            Map<String, String> map = new HashMap<>();
            for (int i = 0; i < titlelist.size(); i++) {
                map.put(titlelist.get(i), rowlist.get(i));
            }

            return map;
        }

        private XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
            XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
            this.sst = sst;
            parser.setContentHandler(this);
            return parser;
        }

        @Override
        public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
            if (name.equals("dimension")) {
                dimension = attributes.getValue("ref");
                String maxRange = dimension.substring(dimension.indexOf(":") + 1);
                maxRows = covertRowIdtoInt(maxRange, true);
                maxCols = covertRowIdtoInt(maxRange, false);
                hCols = (requiredCols <= maxCols && requiredCols > 0) ? requiredCols : maxCols;
            }

            if (name.equals("row")) {
                curRowNum = attributes.getValue("r");
                if ("".equals(preRowNum)) {
                    preRowNum = curRowNum;
                    int isBegin = 0;

                    if ((isBegin = Integer.parseInt(curRowNum) - 1) > 0) {
                        for (int m = 0; m < isBegin; m++)
                            curRow++;
                    }
                }

                int emptyRow = Integer.parseInt(curRowNum) - Integer.parseInt(preRowNum);
                if (emptyRow > 1) {
                    for (int k = 0; k < emptyRow - 1; k++)
                        curRow++;
                }

                preRowNum = curRowNum;
            }

            // c => 单元格
            if (name.equals("c")) {
                setNextDataType(attributes);

                curXy = attributes.getValue("r");
                int curX = covertRowIdtoInt(curXy, false);

                if ("".equals(preXy)) {
                    preXy = curXy;
                    int isStart = 0;

                    if ((isStart = curX - 1) > 0) {
                        for (int n = 0; n < isStart; n++)
                            rowlist.add(curCol++, "");
                    }
                }

                int preX = covertRowIdtoInt(preXy, false);
                int emptyCol = curX - preX;

                if (emptyCol > 1) {
                    for (int x = 0; x < emptyCol - 1; x++)
                        rowlist.add(curCol++, "");
                }

                if (curX - rowlist.size() == 2)
                    rowlist.add(curCol++, "");

                preXy = curXy;
            }

            // 置空
            lastContents = "";
        }

        @Override
        public void endElement(String uri, String localName, String name) throws SAXException {
            if (localName.equals("worksheet")) {
                int emptyRow;
                if (!"".equals(preRowNum) && (emptyRow = maxRows - covertRowIdtoInt(preRowNum, true)) > 0) {
                    for (int i = 0; i < emptyRow; i++)
                        curRow++;
                }

                rowlist.clear();
                optRow(sheetIndex, startRow - 1, rowlist);
                curRow = 0;
                preRowNum = "";
                curRowNum = "";
            }

            // v => 单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引
            // 将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
            if (name.equals("v")) {
                String value = getDataValue(lastContents.trim(), "");
                rowlist.add(curCol++, value);
            } else {
                // 如果标签名称为 row ，这说明已到行尾，调用 optRows() 方法
                if (name.equals("row")) {
                    int emptyCell;
                    if (!"".equals(preXy) && (emptyCell = maxCols - covertRowIdtoInt(preXy, false)) > 0) {
                        for (int m = 0; m < emptyCell; m++)
                            rowlist.add(curCol++, "");
                    }

                    if (maxCols - rowlist.size() == 1)
                        rowlist.add(curCol++, "");

                    optRow(sheetIndex, curRow, rowlist);
                    rowlist.clear();
                    curRow++;
                    curCol = 0;
                    preXy = "";
                    curXy = "";
                    total++;
                }
            }
        }

        @Override
        public void characters(char[] ch, int start, int length) throws SAXException {
            // 得到单元格内容的值
            lastContents += new String(ch, start, length);
        }

        private int covertRowIdtoInt(String rowId, boolean isRowInt) {
            int firstDigit = -1;
            for (int c = 0; c < rowId.length(); ++c) {
                if (Character.isDigit(rowId.charAt(c))) {
                    firstDigit = c;
                    break;
                }
            }

            int result = 0;

            if (isRowInt) {
                result = Integer.parseInt(rowId.substring(firstDigit, rowId.length()));
            } else {
                // AB7-->AB,AB是列号,7是行号
                String newRowId = rowId.substring(0, firstDigit);
                int num = 0;
                int length = newRowId.length();
                for (int i = 0; i < length; i++) {
                    // 先取最低位,B
                    char ch = newRowId.charAt(length - i - 1);
                    // B表示的十进制2,ascii码相减,以A的ascii码为基准,A表示1,B表示2
                    num = (int) (ch - 'A' + 1);
                    // 列号转换相当于26进制数转10进制
                    num *= Math.pow(26, i);
                    result += num;
                }
            }

            return result;
        }

        private void setNextDataType(Attributes attributes) {
            nextDataType = CellDataType.NUMBER;
            formatIndex = -1;
            formatString = null;
            String cellType = attributes.getValue("t");
            String cellStyleStr = attributes.getValue("s");

            if ("b".equals(cellType)) {
                nextDataType = CellDataType.BOOL;
            } else if ("e".equals(cellType)) {
                nextDataType = CellDataType.ERROR;
            } else if ("inlineStr".equals(cellType)) {
                nextDataType = CellDataType.INLINESTR;
            } else if ("s".equals(cellType)) {
                nextDataType = CellDataType.SSTINDEX;
            } else if ("str".equals(cellType)) {
                nextDataType = CellDataType.FORMULA;
            }

            if (cellStyleStr != null) {
                int styleIndex = Integer.parseInt(cellStyleStr);
                XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
                formatIndex = style.getDataFormat();
                formatString = style.getDataFormatString();

                if ("m/d/yy".equals(formatString)) {
                    nextDataType = CellDataType.DATE;
                    formatString = "yyyy-MM-dd";
                }

                if (formatString == null) {
                    nextDataType = CellDataType.NULL;
                    formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
                }
            }
        }

        @SuppressWarnings("deprecation")
        private String getDataValue(String value, String thisStr) {
            switch (nextDataType) {
                // 这几个的顺序不能随便交换，交换了很可能会导致数据错误
                case BOOL:
                    char first = value.charAt(0);
                    thisStr = first == '0' ? "FALSE" : "TRUE";
                    break;
                case ERROR:
                    thisStr = "\"ERROR:" + value.toString() + '"';
                    break;
                case FORMULA:
                    thisStr = '"' + value.toString() + '"';
                    break;
                case INLINESTR:
                    XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
                    thisStr = rtsi.toString();
                    rtsi = null;
                    break;
                case SSTINDEX:
                    String sstIndex = value.toString();
                    try {
                        int idx = Integer.parseInt(sstIndex);
                        thisStr = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
                    } catch (NumberFormatException ex) {
                    }
                    break;
                case NUMBER:
                    if (formatString != null) {
                        thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();
                    } else {
                        thisStr = value;
                    }

                    thisStr = thisStr.replace("_", "").trim();
                    break;
                case DATE:
                    thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
                    break;
                default:
                    thisStr = "";
                    break;
            }

            return thisStr;
        }

        public int getTotal() {
            return this.total;
        }
    }


    /**
     * 数据写入类
     */
    private class DataWriteHandler implements Runnable {
        @Override
        public void run () {
            while (true) {
                List<TaskResultData<T>> taskResultDatas = null;
                String date = sdf.format(new Date());

                try {
                    taskResultDatas = dataQueue.take();
                } catch (InterruptedException e) {
                    logger.debug("receive interrupt message. {} exit blocking", Thread.currentThread().getName());
                    if (dataQueue.size() != 0) {
                        logger.debug("queue is not empty continue attempt blocking");
                        try {
                            taskResultDatas = dataQueue.take();
                        } catch (InterruptedException ex) {
                        }
                    }
                }

                try {
                    if (taskResultDatas != null && taskResultDatas.size() > 0) {
                        handler.writeData(taskResultDatas);
                        totalSuccess += taskResultDatas.size();
                    }
                } catch (Exception e) {
                    logger.error("write data fail. exception: {} cause: {}", e, e.getCause());
                    TaskResult<T> taskResult = new TaskResult<>();

                    taskResult.setBatchNum(UUID.randomUUID().toString().replace("-", ""));
                    taskResult.setType(0);
                    taskResult.setLogTime(date);
                    taskResult.setFailMsg(e.getMessage());
                    taskResult.setInnerError(taskResultDatas);

                    failList.add(taskResult);
                } finally {
                    if (isEnd && dataQueue.size() == 0) {
                        logger.info("write data complete. totalSuccess: {}", totalSuccess);
                        writeEnd = true;
                        break;
                    }
                }
            }
        }
    }


    /**
     * @throws Exception 可能抛出由于空指针或非excel文件引起的异常
     * @return excel.read.Result 返回封装结果
     */
    public Result<T> processExcel() throws Exception {
        Thread writer = new Thread(dataWriteHandler);
        logger.debug("consumer thread start: {}", writer.getName());
        writer.start();
        logger.debug("read excel start");
        excelReadHandler.process();
        logger.debug("read excel end");

        Thread.sleep(1000);
        isEnd = true;
        writer.interrupt();
        while (!writeEnd) ;
        logger.debug("consumer thread end: {}", writer.getName());

        Result<T> result = new Result<T>();
        result.setTotal(excelReadHandler.getTotal() - startRow + 1);
        result.setTotalSuccess(totalSuccess);
        result.setTotalFail(excelReadHandler.getTotal() - startRow - totalSuccess + 1);
        result.setTaskResults(failList);
        logger.info("processExcel success");

        return result;
    }
}
